package com.iplant.aps.serviceimpl.dao.mcs;

import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.Pagination;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.crm.CRMCustomer;
import com.iplant.aps.service.po.mcs.MCSLogInfo;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class MCSLogInfoDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(MCSLogInfoDAO.class);

    private static MCSLogInfoDAO Instance = null;

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, MCSLogInfo wMCSLogInfo, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wMCSLogInfo == null)
                return 0;

            String wSQL = "";
            if (wMCSLogInfo.getID() <= 0) {
                wSQL = MessageFormat.format(
                        "INSERT INTO {0}.mcs_loginfo(CustomerName,LineName,ProductNo,PartNo,VersionNo,FileName," +
                                "FilePath,FileType,CreateTime,CreateTimeStr) VALUES(:CustomerName,:LineName," +
                                ":ProductNo,:PartNo,:VersionNo,:FileName,:FilePath,:FileType,:CreateTime," +
                                ":CreateTimeStr);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format(
                        "UPDATE {0}.mcs_loginfo SET CustomerName = :CustomerName,LineName = :LineName,ProductNo = " +
                                ":ProductNo,PartNo = :PartNo,VersionNo = :VersionNo,FileName = :FileName,FilePath = " +
                                ":FilePath,FileType = :FileType,CreateTime = :CreateTime,CreateTimeStr = " +
                                ":CreateTimeStr WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("ID", wMCSLogInfo.ID);
            wParamMap.put("CustomerName", wMCSLogInfo.CustomerName);
            wParamMap.put("LineName", wMCSLogInfo.LineName);
            wParamMap.put("ProductNo", wMCSLogInfo.ProductNo);
            wParamMap.put("PartNo", wMCSLogInfo.PartNo);
            wParamMap.put("VersionNo", wMCSLogInfo.VersionNo);
            wParamMap.put("FileName", wMCSLogInfo.FileName);
            wParamMap.put("FilePath", wMCSLogInfo.FilePath);
            wParamMap.put("FileType", wMCSLogInfo.FileType);
            wParamMap.put("CreateTime", wMCSLogInfo.CreateTime);
            wParamMap.put("CreateTimeStr", wMCSLogInfo.CreateTimeStr);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wMCSLogInfo.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wMCSLogInfo.setID(wResult);
            } else {
                wResult = wMCSLogInfo.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<MCSLogInfo> wList,
            OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<Integer>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<String>();
            for (MCSLogInfo wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.mcs_loginfo WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public MCSLogInfo SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        MCSLogInfo wResult = new MCSLogInfo();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<MCSLogInfo> wList = SelectList(wLoginUser, wID, "", "", "", "", "", "", "", null, null,
                    Pagination.getMaxSize(), wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<MCSLogInfo> SelectList(BMSEmployee wLoginUser, int wID, String wCustomerName, String wLineName,
            String wProductNo, String wPartNo, String wFileName, String wFileType,
            String wVersionNo,
            Calendar wStartTime,
            Calendar wEndTime, Pagination wPagination,
            OutResult<Integer> wErrorCode) {
        List<MCSLogInfo> wResultList = new ArrayList<MCSLogInfo>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            // 局段处理：将输入的转化为编码+名称两个
            CRMCustomer wCustomer = new CRMCustomer();
            if (StringUtils.isNotEmpty(wCustomerName)) {
                wCustomer = this.CRM_QueryCRMCustomer(wLoginUser, wCustomerName, wErrorCode);
            }

            String wSQL = MessageFormat.format("SELECT * FROM {0}.mcs_loginfo WHERE  1=1  "
                    + "and ( :wID <= 0 or :wID = ID ) "
                    + "and ( :wCustomerName is null or :wCustomerName = '''' or :wCustomerName = CustomerName" +
                    " or :wCustomerCode = CustomerName" +
                    " ) "
                    + "and ( :wLineName is null or :wLineName = '''' or :wLineName = LineName ) "
                    + "and ( :wProductNo is null or :wProductNo = '''' or :wProductNo = ProductNo ) "
                    + "and ( :wPartNo is null or :wPartNo = '''' or  PartNo like ''%{1}%'' ) "
                    + "and ( :wFileName is null or :wFileName = '''' or :wFileName = FileName ) "
                    + "and ( :wFileType is null or :wFileType = '''' or :wFileType = FileType ) "
                    + "and ( :wVersionNo is null or :wVersionNo = '''' or :wVersionNo = VersionNo ) "
                    + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  " +
                    "CreateTime ) "
                    + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  " +
                    "CreateTime )",
                    wInstance.Result, wPartNo);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            if (wCustomer.ID > 0) {
                wCustomerName = wCustomer.CustomerName;
            }

            wParamMap.put("wID", wID);
            wParamMap.put("wCustomerName", wCustomerName);
            wParamMap.put("wCustomerCode", wCustomer.CustomerCode);
            wParamMap.put("wLineName", wLineName);
            wParamMap.put("wProductNo", wProductNo);
            wParamMap.put("wPartNo", wPartNo);
            wParamMap.put("wFileName", wFileName);
            wParamMap.put("wFileType", wFileType);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wVersionNo", wVersionNo);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap, wPagination);

            for (Map<String, Object> wReader : wQueryResult) {
                MCSLogInfo wItem = new MCSLogInfo();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.CustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.VersionNo = StringUtils.parseString(wReader.get("VersionNo"));
                wItem.FileName = StringUtils.parseString(wReader.get("FileName"));
                wItem.FilePath = StringUtils.parseString(wReader.get("FilePath"));
                wItem.FileType = StringUtils.parseString(wReader.get("FileType"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.CreateTimeStr = StringUtils.parseString(wReader.get("CreateTimeStr"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 条件查询集合
     * 优化后支持800万数据快速查询
     */
    public List<MCSLogInfo> SelectListPro(BMSEmployee wLoginUser, int wID, String wCustomerName, String wLineName,
            String wProductNo, String wPartNo, String wFileName, String wFileType,
            String wVersionNo, Calendar wStartTime, Calendar wEndTime,
            Pagination wPagination, OutResult<Integer> wErrorCode) {
        // 预分配合适的初始容量
        List<MCSLogInfo> wResultList = new ArrayList<>(wPagination != null ? wPagination.PageSize : 50);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            // 处理时间参数
            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            Calendar wEffectiveStartTime = (wStartTime != null) ? wStartTime : wBaseTime;
            Calendar wEffectiveEndTime = (wEndTime != null) ? wEndTime : Calendar.getInstance();

            // 时间参数验证
            if (wEffectiveStartTime.compareTo(wEffectiveEndTime) > 0) {
                return wResultList;
            }

            // 构建动态SQL
            StringBuilder wSQL = new StringBuilder(256);
            wSQL.append("SELECT * FROM ").append(wInstance.Result).append(".mcs_loginfo WHERE 1=1 ");

            Map<String, Object> wParamMap = new HashMap<>(16);

            // ID查询优先
            if (wID > 0) {
                wSQL.append(" AND ID = :wID");
                wParamMap.put("wID", wID);
            } else {
                // 时间范围条件
                if (!wEffectiveStartTime.equals(wBaseTime)) {
                    wSQL.append(" AND CreateTime >= :wStartTime");
                    wParamMap.put("wStartTime", wEffectiveStartTime);
                }
                if (!wEffectiveEndTime.equals(wBaseTime)) {
                    wSQL.append(" AND CreateTime <= :wEndTime");
                    wParamMap.put("wEndTime", wEffectiveEndTime);
                }

                // 客户信息处理
                if (StringUtils.isNotEmpty(wCustomerName)) {
                    CRMCustomer wCustomer = this.CRM_QueryCRMCustomer(wLoginUser, wCustomerName, wErrorCode);
                    if (wCustomer.ID > 0) {
                        wSQL.append(" AND (CustomerName = :wCustomerName OR CustomerName = :wCustomerCode)");
                        wParamMap.put("wCustomerName", wCustomer.CustomerName);
                        wParamMap.put("wCustomerCode", wCustomer.CustomerCode);
                    }
                }

                // 精确匹配条件
                if (StringUtils.isNotEmpty(wLineName)) {
                    wSQL.append(" AND LineName = :wLineName");
                    wParamMap.put("wLineName", wLineName);
                }
                if (StringUtils.isNotEmpty(wProductNo)) {
                    wSQL.append(" AND ProductNo = :wProductNo");
                    wParamMap.put("wProductNo", wProductNo);
                }
                if (StringUtils.isNotEmpty(wFileType)) {
                    wSQL.append(" AND FileType = :wFileType");
                    wParamMap.put("wFileType", wFileType);
                }
                if (StringUtils.isNotEmpty(wVersionNo)) {
                    wSQL.append(" AND VersionNo = :wVersionNo");
                    wParamMap.put("wVersionNo", wVersionNo);
                }
                if (StringUtils.isNotEmpty(wFileName)) {
                    wSQL.append(" AND FileName = :wFileName");
                    wParamMap.put("wFileName", wFileName);
                }

                // 模糊查询条件放最后
                if (StringUtils.isNotEmpty(wPartNo)) {
                    wSQL.append(" AND PartNo LIKE :wPartNo");
                    wParamMap.put("wPartNo", "%" + wPartNo + "%");
                }
            }

            // SQL转换
            String wFinalSQL = this.DMLChange(wSQL.toString());

            // 执行查询
            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wFinalSQL, wParamMap, wPagination);

            // 结果映射
            MCSLogInfo wItem;
            for (Map<String, Object> wReader : wQueryResult) {
                wItem = new MCSLogInfo();
                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.CustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.VersionNo = StringUtils.parseString(wReader.get("VersionNo"));
                wItem.FileName = StringUtils.parseString(wReader.get("FileName"));
                wItem.FilePath = StringUtils.parseString(wReader.get("FilePath"));
                wItem.FileType = StringUtils.parseString(wReader.get("FileType"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.CreateTimeStr = StringUtils.parseString(wReader.get("CreateTimeStr"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error("SelectList Error in MCSLogInfoDAO: ", ex);
        }
        return wResultList;
    }

    private CRMCustomer CRM_QueryCRMCustomer(BMSEmployee wLoginUser, String wCustomerName,
            OutResult<Integer> wErrorCode) {
        CRMCustomer wResult = new CRMCustomer();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT * FROM {0}.`crm_customer` WHERE CustomerName=:CustomerName OR " +
                    "CustomerCode=:CustomerName;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("CustomerName", wCustomerName);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.ID = StringUtils.parseInt(wReader.get("ID"));
                wResult.CustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                wResult.CustomerCode = StringUtils.parseString(wReader.get("CustomerCode"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    private MCSLogInfoDAO() {
        super();
    }

    public static MCSLogInfoDAO getInstance() {
        if (Instance == null)
            Instance = new MCSLogInfoDAO();
        return Instance;
    }

    /**
     * 通过BOMID查询传输日志ID
     */
    public int MCS_QueryIDByBOMID(BMSEmployee wLoginUser, int wBOMID, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.mcs_loginfo where find_in_set(''{1}'',BOMID);",
                    wInstance.Result, String.valueOf(wBOMID));

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("ID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 通过BOPID查询传输日志ID
     */
    public Integer MCS_QueryIDByBOPID(BMSEmployee wLoginUser, int wBOPID, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.mcs_loginfo where BOPID=:BOPID;", wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("BOPID", wBOPID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("ID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
